package dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Objects;
import java.util.Vector;

import org.eclipse.jdt.annotation.NonNull;

import bean.Commodity;

public class ShopDAO {
    private Connection conn;

    public ShopDAO(@NonNull Connection conn) {
		this.conn = conn;
	}

	public Commodity selectByCommodityId(int commodityId) throws SQLException {
        Commodity commodity = null;
        if (commodityId < 1) {
            return null;
    	}
        PreparedStatement ps = conn.prepareStatement("SELECT COMMODITY_ID,COMMODITY_NAME,CATEGORY_ID,SALE_PRICE,QUANTITY FROM COMMODITIES WHERE COMMODITY_ID=?");
        ps.setInt(1, commodityId);
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            String commodityName = rs.getString("COMMODITY_NAME");
            int categoryId = rs.getInt("CATEGORY_ID");
            BigDecimal salePrice = rs.getBigDecimal("SALE_PRICE");
            int quantity = rs.getInt("QUANTITY");
            rs.close();
            ps.close();
            ps = conn.prepareStatement("SELECT CATEGORY_NAME FROM CATEGORIES WHERE CATEGORY_ID=?");
            ps.setInt(1, categoryId);
            rs = ps.executeQuery();
            rs.next();
            String categoryName = rs.getString("CATEGORY_NAME");
            commodity = new Commodity(commodityId, commodityName, categoryId, salePrice, quantity, categoryName);
        }
        rs.close();
        ps.close();
        return commodity;
    }

    public Vector<Commodity> selectByKeyword(String keyword, int categoryId) throws SQLException {
        Vector<Commodity> commodities = new Vector<>();
        boolean state1 = Objects.nonNull(keyword) && keyword.length() > 0;
        boolean state2 = categoryId > 0;
        boolean state = state1 && state2;
        String sql = "SELECT A.*,B.CATEGORY_NAME FROM COMMODITIES AS A INNER JOIN CATEGORIES AS B"
            + " ON B.CATEGORY_ID=A.CATEGORY_ID";
        String sql1 = " AND COMMODITY_NAME LIKE ?";
        String sql2 = " AND B.CATEGORY_ID=?";
        keyword = "%" + keyword + "%";
        PreparedStatement ps = conn.prepareStatement(sql);
        if (state) {
            sql += sql1 + sql2;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
            ps.setInt(2, categoryId);
        } else if (state1) {
            sql += sql1;
            ps = conn.prepareStatement(sql);
            ps.setString(1, keyword);
        } else if (state2) {
            sql += sql2;
            ps = conn.prepareStatement(sql);
            ps.setInt(1, categoryId);
        }
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            int commodityId = rs.getInt("COMMODITY_ID");
            String commodityName = rs.getString("COMMODITY_NAME");
            categoryId = rs.getInt("CATEGORY_ID");
            BigDecimal salePrice = rs.getBigDecimal("SALE_PRICE");
            int quantity = rs.getInt("QUANTITY");
            String categoryName = rs.getString("CATEGORY_NAME");
            Commodity commodity = new Commodity(commodityId, commodityName, categoryId, salePrice, quantity, categoryName);
            commodities.addElement(commodity);
        }
        rs.close();
        ps.close();
        return commodities;
    }

    public boolean update(@NonNull Commodity commodity) throws SQLException {
    	PreparedStatement ps = conn.prepareStatement("UPDATE COMMODITIES SET QUANTITY=? WHERE COMMODITY_ID=?");
        ps.setInt(1, commodity.getQuantity());
        ps.setInt(2, commodity.getCommodityId());
        boolean state = ps.executeUpdate() == 1;
        ps.close();
        return state;
    }
}
